package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.TimeSharingGameDTO;
import com.dy.yunying.api.datacenter.vo.TimeSharingGameDataVO;
import com.dy.yunying.api.entity.RealTimeKanbanDO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @author sunyq
 * @date 2022/8/17 15:01
 */
@Slf4j
@Component(value = "timeSharingGameDataDao")
public class TimeSharingGameDataDao {

	@Resource(name = "dorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Autowired
	private YunYingProperties yunYingProperties;

	/**
	 * 列表
	 * @param req
	 * @return
	 */
	public List<TimeSharingGameDataVO> list(TimeSharingGameDTO req) {
		//
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT \n")
				.append("  ").append("`day`,\n")
				.append("  ").append("`hour`,\n")
				.append("  ").append("deviceNums,-- 新增设备数\n")
				.append("  ").append("accountNums,-- 新增账号数 \n")
				.append("  ").append("round( IF ( deviceNums > 0, divide ( accountNums * 100.00, deviceNums ), 0 ), 2 )  AS regRate,-- 注册转换率  新增账号/新增设备\n")
				.append("  ").append("activeFeeAccounts,-- 充值人数\n")
				.append("  ").append("activePayAmounts,-- 充值金额\n")
				.append("  ").append("round( IF ( activeAccounts > 0, divide ( activeFeeAccounts * 100.00, activeAccounts ), 0 ), 2 ) AS payFeeRate,-- 付费率 充值人数/活跃账号\n")
				.append("  ").append("round( IF ( activeAccounts > 0, divide ( activePayAmounts, activeAccounts ), 0 ), 2 ) AS arpu,-- ARPU 活跃充值金额/活跃账号\n")
				.append("  ").append("round( IF ( activeFeeAccounts > 0, divide ( activePayAmounts, activeFeeAccounts ), 0 ), 2 ) AS arppu,-- ARPPU  活跃充值金额/充值人数\n")
				.append("  ").append("newPayNums,-- 新账号充值人数\n")
				.append("  ").append("newPayAmounts,-- 新账号充值金额\n")
				.append("  ").append("round( IF ( accountNums > 0, divide ( newPayNums * 100, accountNums ), 0 ), 2 ) AS newPayFeeRate ,-- 新账号付费率 新账号充值人数/新增账号\n")
				.append("  ").append("round( IF ( accountNums > 0, divide ( newPayAmounts, accountNums ), 0 ), 2 ) AS newArpu,-- 新账号ARPU 新账号充值���额/新增账号\n")
				.append("  ").append("round( IF ( newPayNums > 0, divide ( newPayAmounts, newPayNums ), 0 ), 2 ) AS newArppu,-- 新账号ARPPU 新账号充值金额/新账号充值人数\n")
				.append("  ").append("oldActiveFeeAccounts,-- 老帐号充值人数\n")
				.append("  ").append("oldActivePayAmounts,-- 老帐号充值金额\n")
				.append("  ").append("round( IF ( oldActiveAccounts > 0, divide ( oldActiveFeeAccounts * 100, oldActiveAccounts ), 0 ), 2 ) AS oldPayFeeRate,-- 老帐号付费率 老账号充值人数/老账号数\n")
				.append("  ").append("round( IF ( oldActiveAccounts > 0, divide ( oldActivePayAmounts, oldActiveAccounts ), 0 ), 2 ) AS oldArpu,-- 老账号ARPU 老账号充值金额/老账号数\n")
				.append("  ").append("round( IF ( oldActiveFeeAccounts > 0, divide ( oldActivePayAmounts,oldActiveFeeAccounts ), 0 ), 2 ) AS oldArppu -- 老账号ARPPU 老账号充值金额/老账号充值人数\n")
				.append("FROM  (\n")
				.append("  ").append("SELECT\n")
				.append("  ").append("   IFNULL( a.`day`, b.`day` ) AS `day`,\n")
				.append("  ").append("   IFNULL( a.`hour`, b.`hour` )  AS `hour`,\n")
				.append("  ").append("   COALESCE(a.deviceNums,0) deviceNums,-- 新增设备数\n")
				.append("  ").append("   COALESCE(a.accountNums,0) accountNums,-- 新增账号数\n")
				.append("  ").append("   COALESCE(b.activeAccounts,0) activeAccounts, -- 活跃账号\n")
				.append("  ").append("   COALESCE(b.activeFeeAccounts,0) activeFeeAccounts,-- 充值人数\n")
				.append("  ").append("   COALESCE(b.activePayAmounts,0) activePayAmounts,-- 充值总金额\n")
				.append("  ").append("   COALESCE(b.newPayNums,0) newPayNums,-- 新账号充值人数\n")
				.append("  ").append("   COALESCE(b.newPayAmounts,0) newPayAmounts,-- 新账号充值金额\n")
				.append("  ").append("   COALESCE(b.activeAccounts,0)  - COALESCE(a.accountNums,0)  AS  oldActiveAccounts,-- 老账号数\n")
				.append("  ").append("   COALESCE(b.activeFeeAccounts,0) -  COALESCE(b.newPayNums,0)  AS oldActiveFeeAccounts,-- 老帐号充值人数\n")
				.append("  ").append("   COALESCE(b.activePayAmounts,0)  - COALESCE(b.newPayAmounts,0)  AS oldActivePayAmounts -- 老帐号充值金额\n")
				.append("  ").append("FROM ( \n")
				.append("  ").append("   SELECT\n\n")
				.append(" ").append("       '").append(req.getStartTime()).append("' AS DAY,\n")
				.append(" ").append("       IFNULL(a.`hour`,b.`hour`) as `hour`,")
				.append(" ").append("       a.deviceNums,\n")
				.append(" ").append("       b.accountNums \n")
				.append(" ").append("   FROM ( \n")
				.append(" ").append("      SELECT\n")
				.append(" ").append("          COUNT( DISTINCT uuid ) AS deviceNums,\n")
				.append(" ").append("          a.reg_date AS `day`,\n")
				.append(" ").append("          a.`hour` as `hour`\t\n")
				.append(" ").append("      FROM \n")
				.append(" ").append("         ").append(yunYingProperties.getDailyDataDeviceRegTableData()).append(" a\n")
				.append(" ").append("      WHERE \n")
				.append(" ").append("          a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          AND a.reg_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("          ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("       GROUP BY \n")
				.append(" ").append("          a.reg_date , a.`hour`\n")
				.append(" ").append("       ) a\n")
				.append(" ").append("       FULL JOIN (\n")
				.append(" ").append("          SELECT\n\n")
				.append(" ").append("             COUNT( DISTINCT username ) AS accountNums, -- 新增账号\n")
				.append(" ").append("             a.reg_date AS `day` ,\n")
				.append(" ").append("             a.`hour` as `hour` \n")
				.append(" ").append("          FROM\n ")
				.append(" ").append("          	  ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a\n")
				.append(" ").append("          WHERE\n ")
				.append(" ").append("          	   a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          	   AND a.reg_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("              ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("          GROUP BY \n")
				.append(" ").append("              a.reg_date , a.`hour`\n")
				.append(" ").append("       ) b USING ( `day`,`hour` ) \n")
				.append(" ").append("    ) a\n")
				.append(" ").append("    FULL JOIN (\n")
				.append(" ").append("       SELECT\n")
				.append(" ").append("           `day`,\n")
				.append(" ").append("           `hour`,\n")
				.append(" ").append("           COUNT( DISTINCT newpaynum ) AS newPayNums,-- 新账号充值人数\n")
				.append(" ").append("           sum( newpayamount ) AS newPayAmounts,-- 新账号充值金额\n")
				.append(" ").append("           COUNT( DISTINCT paynum ) AS activeFeeAccounts,-- 活跃付费总人数\n")
				.append(" ").append("           SUM( pay_amount ) AS activePayAmounts,-- 付费总金额\n")
				.append(" ").append("           COUNT( DISTINCT usename ) activeAccounts -- 活跃账号\n")
				.append(" ").append("       FROM (\n")
				.append(" ").append("           SELECT\n")
				.append(" ").append("              a.active_date AS DAY,\n")
				.append(" ").append("              a.`hour` as `hour`,\n")
				.append(" ").append("              a.username usename,-- 活跃账号数,\n")
				.append(" ").append("              a.pay_amount,-- 付费金额\n")
				.append(" ").append("              IF( a.pay_amount > 0, a.username, NULL ) paynum, -- 付费人数\n")
				.append(" ").append("              IF( a.active_date = b.reg_date AND a.pay_amount > 0, a.username, NULL ) newpaynum,-- 新付费人\n")
				.append(" ").append("              IF( a.active_date = b.reg_date AND a.pay_amount > 0, pay_amount, NULL ) newpayamount -- 新付费金额\n")
				.append(" ").append("             FROM ( \n")
				.append(" ").append("                 SELECT\n")
				.append(" ").append("                     active_date,\n")
				.append(" ").append("                     username,\n")
				.append(" ").append("                     pay_amount,\n")
				.append(" ").append("                     app_main,\n")
				.append(" ").append("                     app_code ,\n")
				.append(" ").append("                     `hour`\n")
				.append(" ").append("                 FROM\n")
				.append(" ").append("                     ").append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append(" ").append("                 WHERE\n")
				.append(" ").append("                     a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("                     AND a.active_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("                     ").append(selectCommonCondition(req,2)).append("\n")
				.append(" ").append("                  ) a\n")
				.append(" ").append("               ").append(StringUtils.isEmpty(req.getParentchlArr()) ? " left " : " inner ").append(" JOIN ( \n")
				.append(" ").append("                  SELECT \n")
				.append(" ").append("                      reg_date, \n")
				.append(" ").append("                      username, \n")
				.append(" ").append("                      app_main, \n")
				.append(" ").append("                      app_code \n")
				.append(" ").append("                  FROM \n")
				.append(" ").append("                      ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a \n")
				.append(" ").append("                  WHERE \n")
				.append(" ").append("                      a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("                      AND a.reg_date <= '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("                      ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("                  ) b ON a.app_main = b.app_main AND a.app_code = b.app_code AND a.username = b.username \n")
				.append(" ").append("            ) a\n")
				.append(" ").append("             GROUP BY\n")
				.append(" ").append("            a.`day`,a.`hour` \n")
				.append("      ) b USING ( `day`,`hour` ) ) a ");


//		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
//			sql.append("ORDER BY\n");
//			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
//		} else {
//			sql.append("ORDER BY\n");
//			sql.append("   `hour` DESC\n");
//		}

		log.info("分时列表数据查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<TimeSharingGameDataVO> timeSharingGameDataVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(TimeSharingGameDataVO.class));
		log.info("分时列表数据查询耗时: {}ms", System.currentTimeMillis() - start);
		return timeSharingGameDataVOList;
	}

	private String selectCommonCondition(TimeSharingGameDTO req, int num) {
		String gameIdArr = req.getGameidArr();
		String pgIdArr = req.getPgidArr();
		String parentChlArr = req.getParentchlArr();
		String chlArr = req.getChlArr();
		String appChlArr = req.getAppchlArr();
		StringBuilder commonCondition = new StringBuilder();
		if (StringUtils.isNotEmpty(pgIdArr)){
			commonCondition.append("and a.app_main in ( ").append(pgIdArr).append(" )")
					.append(StringUtils.SPACE);
		}
		if (StringUtils.isNotEmpty(gameIdArr)){
			commonCondition.append("and a.app_code in ( ").append(gameIdArr).append(" )")
					.append(StringUtils.SPACE);
		}

		if (num == 2){
			return commonCondition.toString();
		}
		if (StringUtils.isNotEmpty(parentChlArr)){
			String[] split = parentChlArr.split(",");
			if (split !=null && split.length>0){
				commonCondition.append("and a.chl_main in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}

		}
		if (StringUtils.isNotEmpty(chlArr)){
			String[] split = chlArr.split(",");
			if (split !=null && split.length>0){
				commonCondition.append("and a.chl_sub in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}
		}
		if (StringUtils.isNotEmpty(appChlArr)){
			String[] split = appChlArr.split(",");
			if (split !=null && split.length>0){
				commonCondition.append("and a.chl_app in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);
			}
		}


		return commonCondition.toString();


	}

	public TimeSharingGameDataVO collect(TimeSharingGameDTO req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT \n")
				.append("  '汇总' AS day,\n")
				.append("  ").append(" '-' `hour`,\n")
				.append("  ").append("deviceNums,-- 新增设备数\n")
				.append("  ").append("accountNums,-- 新增账号数 \n")
				.append("  ").append("round( IF ( deviceNums > 0, divide ( accountNums * 100.00, deviceNums ), 0 ), 2 )  AS regRate,-- 注册转换率  新增账号/新增设备\n")
				.append("  ").append("activeFeeAccounts,-- 充值人数\n")
				.append("  ").append("activePayAmounts,-- 充值金额\n")
				.append("  ").append("round( IF ( activeAccounts > 0, divide ( activeFeeAccounts * 100.00, activeAccounts ), 0 ), 2 ) AS payFeeRate,-- 付费率 充值人数/活跃账号\n")
				.append("  ").append("round( IF ( activeAccounts > 0, divide ( activePayAmounts, activeAccounts ), 0 ), 2 ) AS arpu,-- ARPU 活跃充值金额/活跃账号\n")
				.append("  ").append("round( IF ( activeFeeAccounts > 0, divide ( activePayAmounts, activeFeeAccounts ), 0 ), 2 ) AS arppu,-- ARPPU  活跃充值金额/充值人数\n")
				.append("  ").append("activeFeeAccounts as totalFeeAccounts, -- 累计充值人数\n")
				.append("  ").append("activePayAmounts as totalPayAmounts, -- 累计充值金额\n")
				.append("  ").append("newPayNums,-- 新账号充值人数\n")
				.append("  ").append(" newPayAmounts,-- 新账号充值金额\n")
				.append("  ").append("round( IF ( accountNums > 0, divide ( newPayNums * 100, accountNums ), 0 ), 2 ) AS newPayFeeRate ,-- 新账号付费率 新账号充值人数/新增账号\n")
				.append("  ").append("round( IF ( accountNums > 0, divide ( newPayAmounts, accountNums ), 0 ), 2 ) AS newArpu,-- 新账号ARPU 新账号充值���额/新增账号\n")
				.append("  ").append("round( IF ( newPayNums > 0, divide ( newPayAmounts, newPayNums ), 0 ), 2 ) AS newArppu,-- 新账号ARPPU 新账号充值金额/新账号充值人数\n")
				.append("  ").append("oldActiveFeeAccounts,-- 老帐号充值人数\n")
				.append("  ").append("oldActivePayAmounts,-- 老帐号充值金额\n")
				.append("  ").append("round( IF ( oldActiveAccounts > 0, divide ( oldActiveFeeAccounts * 100, oldActiveAccounts ), 0 ), 2 ) AS oldPayFeeRate,-- 老帐号付费率 老账号充值人数/老账号数\n")
				.append("  ").append("round( IF ( oldActiveAccounts > 0, divide ( oldActivePayAmounts, oldActiveAccounts ), 0 ), 2 ) AS oldArpu,-- 老账号ARPU 老账号充值金额/老账号数\n")
				.append("  ").append("round( IF ( oldActiveFeeAccounts > 0, divide ( oldActivePayAmounts,oldActiveFeeAccounts ), 0 ), 2 ) AS oldArppu -- 老账号ARPPU 老账号充值金额/老账号充值人数\n")
				.append("FROM  (\n")
				.append("  ").append("SELECT\n")
				.append("  ").append("   a.`day` as `day`,\n")
				.append("  ").append("   COALESCE(a.deviceNums,0) deviceNums,-- 新增设备数\n")
				.append("  ").append("   COALESCE(a.accountNums,0) accountNums,-- 新增账号数\n")
				.append("  ").append("   COALESCE(b.activeAccounts,0) activeAccounts, -- 活跃账号\n")
				.append("  ").append("   COALESCE(b.activeFeeAccounts,0) activeFeeAccounts,-- 充值人数\n")
				.append("  ").append("   COALESCE(b.activePayAmounts,0) activePayAmounts,-- 充值总金额\n")
				.append("  ").append("   COALESCE(b.newPayNums,0) newPayNums,-- 新账号充值人数\n")
				.append("  ").append("   COALESCE(b.newPayAmounts,0) newPayAmounts,-- 新账号充值金额\n")
				.append("  ").append("   COALESCE(b.activeAccounts,0)  - COALESCE(a.accountNums,0)  AS  oldActiveAccounts,-- 老账号数\n")
				.append("  ").append("   COALESCE(b.activeFeeAccounts,0) -  COALESCE(b.newPayNums,0)  AS oldActiveFeeAccounts,-- 老帐号充值人数\n")
				.append("  ").append("   COALESCE(b.activePayAmounts,0)  - COALESCE(b.newPayAmounts,0)  AS oldActivePayAmounts -- 老帐号充值金额\n")
				.append("  ").append("FROM ( \n")
				.append("  ").append("   SELECT\n\n")
				.append(" ").append("       '").append(req.getStartTime()).append("' AS DAY,\n")
				.append(" ").append("       a.deviceNums,\n")
				.append(" ").append("       b.accountNums \n")
				.append(" ").append("   FROM ( \n")
				.append(" ").append("      SELECT\n")
				.append(" ").append("          COUNT( DISTINCT uuid ) AS deviceNums,\n")
				.append(" ").append("          a.reg_date AS `day`\n")
				.append(" ").append("      FROM \n")
				.append(" ").append("         ").append(yunYingProperties.getDailyDataDeviceRegTableData()).append(" a\n")
				.append(" ").append("      WHERE \n")
				.append(" ").append("          a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          AND a.reg_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("          ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("       GROUP BY \n")
				.append(" ").append("          a.reg_date \n")
				.append(" ").append("       ) a\n")
				.append(" ").append("       FULL JOIN (\n")
				.append(" ").append("          SELECT\n\n")
				.append(" ").append("             COUNT( DISTINCT username ) AS accountNums, -- 新增账号\n")
				.append(" ").append("             a.reg_date AS `day`\n")
				.append(" ").append("          FROM\n ")
				.append(" ").append("          	  ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a\n")
				.append(" ").append("          WHERE\n ")
				.append(" ").append("          	   a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          	   AND a.reg_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("              ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("          GROUP BY \n")
				.append(" ").append("              a.reg_date \n")
				.append(" ").append("       ) b USING ( `day` ) \n")
				.append(" ").append("    ) a\n")
				.append(" ").append("    FULL JOIN (\n")
				.append(" ").append("       SELECT\n")
				.append(" ").append("           `day`,\n")
				.append(" ").append("           COUNT( DISTINCT newpaynum ) AS newPayNums,-- 新账号充值人数\n")
				.append(" ").append("           sum( newpayamount ) AS newPayAmounts,-- 新账号充值金额\n")
				.append(" ").append("           COUNT( DISTINCT paynum ) AS activeFeeAccounts,-- 活跃付费总人数\n")
				.append(" ").append("           SUM( pay_amount ) AS activePayAmounts,-- 付费总金额\n")
				.append(" ").append("           COUNT( DISTINCT usename ) activeAccounts -- 活跃账号\n")
				.append(" ").append("       FROM (\n")
				.append(" ").append("           SELECT\n")
				.append(" ").append("              a.active_date AS DAY,\n")
				.append(" ").append("              a.username usename,-- 活跃账号数,\n")
				.append(" ").append("              a.pay_amount,-- 付费金额\n")
				.append(" ").append("              IF( a.pay_amount > 0, a.username, NULL ) paynum, -- 付费人数\n")
				.append(" ").append("              IF( a.active_date = b.reg_date AND a.pay_amount > 0, a.username, NULL ) newpaynum,-- 新付费人\n")
				.append(" ").append("              IF( a.active_date = b.reg_date AND a.pay_amount > 0, pay_amount, NULL ) newpayamount -- 新付费金额\n")
				.append(" ").append("            FROM ( \n")
				.append(" ").append("                SELECT\n")
				.append(" ").append("                    active_date,\n")
				.append(" ").append("                    username,\n")
				.append(" ").append("                    pay_amount,\n")
				.append(" ").append("                    app_main,\n")
				.append(" ").append("                    app_code \n")
				.append(" ").append("                FROM\n")
				.append(" ").append("                    ").append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append(" ").append("                WHERE\n")
				.append(" ").append("                    a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("                    AND a.active_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("                    ").append(selectCommonCondition(req,2)).append("\n")
				.append(" ").append("                 ) a\n")
				.append(" ").append("             ").append(StringUtils.isEmpty(req.getParentchlArr()) ? " left " : " inner ").append(" JOIN ( \n")
				.append(" ").append("                 SELECT \n")
				.append(" ").append("                     reg_date, \n")
				.append(" ").append("                     username, \n")
				.append(" ").append("                     app_main, \n")
				.append(" ").append("                     app_code \n")
				.append(" ").append("                 FROM \n")
				.append(" ").append("                     ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a \n")
				.append(" ").append("                 WHERE \n")
				.append(" ").append("                     a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("                     AND a.reg_date <= '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("                     ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("                ) b ON a.app_main = b.app_main AND a.app_code = b.app_code AND a.username = b.username \n")
				.append(" ").append("            ) a\n")
				.append(" ").append("           GROUP BY\n")
				.append(" ").append("              a.`day`\n")
				.append("            ) b USING ( `day`) ) a ");
		log.info("分时列表数据查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<TimeSharingGameDataVO> timeSharingGameDataVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(TimeSharingGameDataVO.class));
		log.info("分时列表数据查询耗时: {}ms", System.currentTimeMillis() - start);
		if (CollectionUtils.isNotEmpty(timeSharingGameDataVOList)){
			return timeSharingGameDataVOList.get(0);
		}
		return new TimeSharingGameDataVO();

	}


	/**
	 * 累计
	 * @param req
	 * @return
	 */
	public Map<String,Object> totalData(TimeSharingGameDTO req){

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT \n")
				.append("  ").append("count(DISTINCT h0) as '01:00',\n")
				.append("  ").append("count(DISTINCT h1) as '02:00',\n")
				.append("  ").append("count(DISTINCT h2) as '03:00',\n")
				.append("  ").append("count(DISTINCT h3) as '04:00',\n")
				.append("  ").append("count(DISTINCT h4) as '05:00',\n")
				.append("  ").append("count(DISTINCT h5) as '06:00',\n")
				.append("  ").append("count(DISTINCT h6) as '07:00',\n")
				.append("  ").append("count(DISTINCT h7) as '08:00',\n")
				.append("  ").append("count(DISTINCT h8) as '09:00',\n")
				.append("  ").append("count(DISTINCT h9) as '10:00',\n")
				.append("  ").append("count(DISTINCT h10) as '11:00',\n")
				.append("  ").append("count(DISTINCT h11) as '12:00',\n")
				.append("  ").append("count(DISTINCT h12) as '13:00',\n")
				.append("  ").append("count(DISTINCT h13) as '14:00',\n")
				.append("  ").append("count(DISTINCT h14) as '15:00',\n")
				.append("  ").append("count(DISTINCT h15) as '16:00',\n")
				.append("  ").append("count(DISTINCT h16) as '17:00',\n")
				.append("  ").append("count(DISTINCT h17) as '18:00',\n")
				.append("  ").append("count(DISTINCT h18) as '19:00',\n")
				.append("  ").append("count(DISTINCT h19) as '20:00',\n")
				.append("  ").append("count(DISTINCT h20) as '21:00',\n")
				.append("  ").append("count(DISTINCT h21) as '22:00',\n")
				.append("  ").append("count(DISTINCT h22) as '23:00',\n")
				.append("  ").append("count(DISTINCT h23) as '24:00'\n")
				.append("  ").append("FROM (\n")
				.append("  ").append("   SELECT \n")
				.append("  ").append("      if(`hour`<=0 and pay_amount > 0 ,username,null) as h0,\n")
				.append("  ").append("      if(`hour`<=1 and pay_amount > 0 ,username,null) as h1 ,\n")
				.append("  ").append("      if(`hour`<=2 and pay_amount > 0 ,username,null) as h2 ,\n")
				.append("  ").append("      if(`hour`<=3 and pay_amount > 0,username,null) as h3 ,\n")
				.append("  ").append("      if(`hour`<=4 and pay_amount > 0,username,null) as h4 ,\n")
				.append("  ").append("      if(`hour`<=5 and pay_amount > 0,username,null) as h5 ,\n")
				.append("  ").append("      if(`hour`<=6 and pay_amount > 0,username,null) as h6 ,\n")
				.append("  ").append("      if(`hour`<=7 and pay_amount > 0,username,null) as h7 ,\n")
				.append("  ").append("      if(`hour`<=8 and pay_amount > 0,username,null) as h8 ,\n")
				.append("  ").append("      if(`hour`<=9 and pay_amount > 0,username,null) as h9 ,\n")
				.append("  ").append("      if(`hour`<=10 and pay_amount > 0,username,null) as h10 ,\n")
				.append("  ").append("      if(`hour`<=11 and pay_amount > 0,username,null) as h11 ,\n")
				.append("  ").append("      if(`hour`<=12 and pay_amount > 0,username,null) as h12 ,\n")
				.append("  ").append("      if(`hour`<=13 and pay_amount > 0,username,null) as h13 ,\n")
				.append("  ").append("      if(`hour`<=14 and pay_amount > 0,username,null) as h14 ,\n")
				.append("  ").append("      if(`hour`<=15 and pay_amount > 0,username,null) as h15 ,\n")
				.append("  ").append("      if(`hour`<=16 and pay_amount > 0,username,null) as h16 ,\n")
				.append("  ").append("      if(`hour`<=17 and pay_amount > 0,username,null) as h17 ,\n")
				.append("  ").append("      if(`hour`<=18 and pay_amount > 0,username,null) as h18 ,\n")
				.append("  ").append("      if(`hour`<=19 and pay_amount > 0,username,null) as h19 ,\n")
				.append("  ").append("      if(`hour`<=20 and pay_amount > 0,username,null) as h20 ,\n")
				.append("  ").append("      if(`hour`<=21 and pay_amount > 0,username,null) as h21 ,\n")
				.append("  ").append("      if(`hour`<=22 and pay_amount > 0,username,null) as h22 ,\n")
				.append("  ").append("      if(`hour`<=23 and pay_amount > 0,username,null) as h23 \n")
				.append("  ").append("    FROM  (\n")
				.append("  ").append("        SELECT \n")
				.append("  ").append("          a.username,\n")
				.append("  ").append("          a.pay_amount,\n")
				.append("  ").append("          a.`hour`\n")
				.append("  ").append("        FROM (\n")
				.append("  ").append("          SELECT\n")
				.append("  ").append("             username,\n")
				.append("  ").append("             app_main,\n")
				.append("  ").append("             app_code,\n")
				.append("  ").append("             pay_amount,\n")
				.append("  ").append("             `hour`\n")
				.append("  ").append("           FROM\n")
				.append(" ").append("               ").append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append(" ").append("            WHERE\n")
				.append(" ").append("                a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("                AND a.active_date = '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("                ").append(selectCommonCondition(req,2)).append("\n")
				.append(" ").append("           ) a\n")
				.append(" ").append("            ").append(StringUtils.isEmpty(req.getParentchlArr()) ? " " : joinSql(req)).append("\n")
				.append(" ").append(" ) a  ) a \n");

		log.info("分时列表累计数据查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		Map<String, Object> map = dorisTemplate.queryForMap(sql.toString());
		//List<TimeSharingGameDataVO> timeSharingGameDataVOList = dorisDailyDataTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(TimeSharingGameDataVO.class));
		log.info("分时列表累计数据查询耗时: {}ms", System.currentTimeMillis() - start);
		return map;

	}

	public String joinSql(TimeSharingGameDTO req){
		StringBuilder joinSql = new StringBuilder();
		joinSql.append("  ").append("INNER JOIN (\n")
				.append("  ").append("   SELECT \n")
				.append("  ").append("      username, \n ")
				.append("  ").append("      app_main, \n ")
				.append("  ").append("      app_code \n")
				.append("  ").append("   FROM \n")
				.append("         ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a \n")
				.append(" ").append("    WHERE \n")
				.append(" ").append("        a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("        AND a.reg_date <= '").append(req.getStartTime()).append("'").append("\n")
				.append(" ").append("        ").append(selectCommonCondition(req,1)).append("\n")
				.append(" ").append("  ) b ON a.app_main = b.app_main AND a.app_code = b.app_code AND a.username = b.username \n");
		return joinSql.toString();
	}
}
